﻿Imports System.Data.OleDb
Imports System.Data


Public NotInheritable Class DataAccessLayer

    Shared connString As String = ConfigurationManager.ConnectionStrings("ConnStringWorldsCricket").ConnectionString.ToString()


    Public Shared Function ExecuteDataSet(ByVal sqlStatement As String, ByVal ParamArray arrParam() As OleDbParameter) As DataSet
        Dim ds As New DataSet
        Using cnn As OleDbConnection = New OleDbConnection(connString)
            ' Define the command 
            Using cmd As New OleDbCommand
                cmd.CommandType = CommandType.Text
                cmd.CommandText = sqlStatement
                cmd.Connection = cnn

                ' Handle the parameters 
                If arrParam IsNot Nothing Then
                    For Each param As OleDbParameter In arrParam
                        cmd.Parameters.Add(param)
                    Next
                End If
                cnn.Open()
                ' Define the data adapter and fill the dataset 
                Using da As New OleDbDataAdapter(cmd)
                    da.Fill(ds)
                End Using
                cnn.Close()
                If arrParam IsNot Nothing Then
                    Array.Clear(arrParam, 0, arrParam.Length)
                End If
                cmd.Parameters.Clear()
                cmd.Dispose()
            End Using
        End Using
        Return ds
    End Function

    Public Shared Function ExecuteNonQuery(ByVal sqlStatement As String, ByVal ParamArray arrParam() As OleDbParameter) As Integer
        Dim numberOFRowsEffected As Integer
        Using cnn As OleDbConnection = New OleDbConnection(connString)

            ' Define the command 
            Using cmd As New OleDbCommand
                cmd.CommandType = CommandType.Text
                cmd.CommandText = sqlStatement
                cmd.Connection = cnn

                ' Handle the parameters 
                If arrParam IsNot Nothing Then
                    For Each param As OleDbParameter In arrParam
                        cmd.Parameters.Add(param)
                    Next
                End If
                cnn.Open()
                numberOFRowsEffected = cmd.ExecuteNonQuery()
                cnn.Close()
                cmd.Parameters.Clear()
                cmd.Dispose()

            End Using
        End Using
        Return numberOFRowsEffected
    End Function

    Public Shared Function ExecuteScalar(ByVal sqlStatement As String, ByVal ParamArray arrParam() As OleDbParameter) As String
        Dim result As String
        Using cnn As OleDbConnection = New OleDbConnection(connString)
            ' Define the command 
            Using cmd As New OleDbCommand
                cmd.CommandType = CommandType.Text
                cmd.CommandText = sqlStatement
                cmd.Connection = cnn

                ' Handle the parameters 
                If arrParam IsNot Nothing Then
                    For Each param As OleDbParameter In arrParam
                        cmd.Parameters.Add(param)
                    Next
                End If
                cnn.Open()
                result = cmd.ExecuteScalar()
                cnn.Close()

                If arrParam IsNot Nothing Then
                     Array.Clear(arrParam, 0, arrParam.Length)
                End If
                cmd.Parameters.Clear()
                cmd.Dispose()

            End Using
        End Using
        Return result
    End Function

    Public Shared Function ExecuteDataReader(ByVal sqlStatement As String, ByVal ParamArray arrParam() As OleDbParameter) As OleDbDataReader
        Dim result As OleDbDataReader
        Dim cnn As OleDbConnection = New OleDbConnection(connString)
        ' Define the command 
        Dim cmd As New OleDbCommand
        cmd.CommandType = CommandType.Text
        cmd.CommandText = sqlStatement
        cmd.Connection = cnn

        ' Handle the parameters 
        If arrParam IsNot Nothing Then
            For Each param As OleDbParameter In arrParam
                cmd.Parameters.Add(param)
            Next
        End If
        cnn.Open()
        result = cmd.ExecuteReader()

        If arrParam IsNot Nothing Then
            Array.Clear(arrParam, 0, arrParam.Length)
        End If
        cmd.Parameters.Clear()
        cmd.Dispose()

        Return result
    End Function




End Class
